﻿Imports System.Data
Imports System.Data.SqlClient
Public Class DALUsuario
    Dim cmd As SqlCommand
    Dim ret As Boolean

    Function CadastrarUsuario(ByVal login As String, ByVal senha As String, ByVal status As String, ByVal permissao As String, ByVal data As String)
        Try
            Dim tab As DataTable
            cmd = New SqlCommand
            tab = localizar(login)
            If tab.Rows.Count = 0 Then
                cmd.CommandText = "insert into gen_usuario(usu_login,usu_senha,usu_status,usu_permissao,usu_datacadastro)" & _
                               " values (@login,@senha,@status,@permissao,@datacad);"
                cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = login
                cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar)).Value = senha
                cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.VarChar)).Value = status
                cmd.Parameters.Add(New SqlParameter("@permissao", SqlDbType.VarChar)).Value = permissao
                cmd.Parameters.Add(New SqlParameter("@datacad", SqlDbType.VarChar)).Value = data
                ret = objconAgenda.executaCmd(cmd)
                If ret = True Then
                    Return True
                Else
                    Return False
                End If
            Else
                Return False
            End If

        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Function logar(ByVal login As String, ByVal senha As String) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select u.usu_codigo,u.usu_login,p.per_descricao,s.sta_descricao from Age_Usuario as u inner join Age_Permissao as p" & _
  " on u.per_codigo = p.per_codigo inner join Gen_Status s on s.sta_codigo = u.sta_codigo where u.usu_login = @login and PWDCOMPARE(@senha, u.usu_senha)= 1;"
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = login
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar)).Value = senha
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function AlterarSenha(ByVal senha As String, ByVal senhaantiga As String, ByVal login As String) As Integer

        Try
            cmd = New SqlCommand
            cmd.CommandText = "update gen_usuario set usu_senha = @senha where usu_senha = @senhaantiga and usu_login=@login"
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar)).Value = senha
            cmd.Parameters.Add(New SqlParameter("@senhaantiga", SqlDbType.VarChar)).Value = senhaantiga
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = login
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from  gen_usuario where " & _
                " usu_codigo like @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab

    End Function
    Function Bloquear(ByVal codigo As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim ret As Boolean
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update gen_usuario set usu_status='Desativado' where " & _
                " usu_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function Ativar(ByVal codigo As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim ret As Boolean
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update gen_usuario set usu_status='Ativo' where " & _
                " usu_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function


    Function localizar(ByVal login As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select usu_codigo,usu_login,usu_status,usu_permissao,usu_datacadastro from  gen_usuario where " & _
                " usu_login like @login"
            cmd.Parameters.Add(New SqlParameter("@login", SqlDbType.VarChar)).Value = "%" & login & "%"
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
End Class
